Skip to main content

Query Optimization

Avoid Functions in WHERE Clause​

When you wrap a column in a function, indexes cannot be used.

Bad:

SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

MySQL must evaluate YEAR() for every row → slow.

Good:

SELECT * FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date <= '2020-12-31';

Uses index on hire_date. Much faster.

Use Query Caching (if available)​

  • In older MySQL versions → Query Cache can store results.
  • In MySQL 8+ → use external caching (e.g., Redis, Memcached).

Example:

Instead of repeatedly running:

SELECT COUNT(*) FROM orders WHERE status = 'Pending';

Cache the result in Redis and refresh periodically.

Avoid N+1 Query Problem​

Bad practice: running multiple queries in a loop.

Bad:

-- For each employee, fetch department
SELECT dept_name FROM departments WHERE id = 101;
SELECT dept_name FROM departments WHERE id = 102;
SELECT dept_name FROM departments WHERE id = 103;

This runs many queries → very slow.

Good (single JOIN query):

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

One query instead of many. Huge performance boost.